﻿Imports System.Data.SqlClient
Imports extranet.BE
Imports MySql.Data.MySqlClient

Public Class DATela
  Private objBD As New BancoDeDados
  Public objQuery As New BEQuery

  Sub New()
  End Sub

  Sub New(ByVal booCondicaoSimilar As Boolean)
    objQuery.Where.CondicaoSimilar = booCondicaoSimilar
  End Sub

  Public Sub Cadastra(ByRef objBETela As BETela)
    Try
      objBD.duplicaAspasSimples(objBETela, GetType(BETela))
      Dim strSQL = "INSERT INTO ext_telas_sistema (nome, descricao, modulo) " & _
                   "VALUES ('" & objBETela.Nome & "', '" & _
                                 objBETela.Descricao & "', '" & _
                                 objBETela.Modulo & "');" & _
                   "SELECT LAST_INSERT_ID();"
      objBD.InicializarConexao()
      objBD.InicializarTransacao()
      Try
        Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
        objBETela.IdTela = objComando.ExecuteScalar()

        relacionaGruposAcesso(objBETela)

        objBD.Commit()
      Catch ex As ApplicationException
        objBD.Rollback()
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        objBD.FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DATela.Cadastra(): " & ex.Message)
    End Try
  End Sub

  Public Sub Atualiza(ByVal objBETela As BETela)
    Try
      objBD.duplicaAspasSimples(objBETela, GetType(BETela))
      Dim strSQL = "UPDATE ext_telas_sistema " & _
                   "SET nome='" & objBETela.Nome & "', " & _
                       "descricao='" & objBETela.Descricao & "', " & _
                       "modulo='" & objBETela.Modulo & "' " & _
                   "WHERE id_tela=" & objBETela.IdTela & ";"
      objBD.InicializarConexao()
      objBD.InicializarTransacao()
      Try
        Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
        objComando.ExecuteNonQuery()

        relacionaGruposAcesso(objBETela)

        objBD.Commit()
      Catch ex As ApplicationException
        objBD.Rollback()
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        objBD.FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DATela.Atualiza(): " & ex.Message)
    End Try
  End Sub

  Private Sub relacionaGruposAcesso(ByVal objBETela As BETela)
    Dim strSQL As String = "DELETE FROM ext_grupo_acesso_telas_sistema WHERE id_tela=" & objBETela.IdTela & ";"
    Try
      For Each objBEGrupoAcesso As BEGrupoAcesso In objBETela.GruposAcesso
        With objBEGrupoAcesso
          strSQL &= "INSERT INTO ext_grupo_acesso_telas_sistema " & _
                      "(id_grupo, id_tela, leitura, escrita, modificacao, exclusao) VALUES (" & _
                      .IdGrupo & ", " & objBETela.IdTela & ", " & IIf(.Leitura, "1", "0") & ", " & _
                      IIf(.Escrita, "1", "0") & ", " & IIf(.Modificacao, "1", "0") & ", " & _
                      IIf(.Exclusao, "1", "0") & ");"
        End With
      Next
      Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
      objComando.ExecuteNonQuery()
    Catch ex As ApplicationException
      Throw New ApplicationException("Erro ao executar o relacionamento de Grupos de Acesso: " & ex.Message)
    End Try
  End Sub

  Public Sub Exclui(ByVal objBETela As BETela)
    Try
      objBD.ExecutaNonQuery("DELETE FROM ext_grupo_acesso_telas_sistema WHERE id_tela=" & objBETela.IdTela & ";" & _
                            "DELETE FROM ext_telas_sistema WHERE id_tela='" & objBETela.IdTela & "';")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DATela.Exclui(): " & ex.Message)
    End Try
  End Sub

  Public Function SelecionaTela(ByVal objBETela As BETela) As BETela
    Try
      objQuery.Where.CondicaoSimilar = False
      Dim objBETelas As List(Of BETela) = Seleciona(objBETela)
      If objBETelas.Count > 0 Then
        objBETela = objBETelas(0)
      Else
        objBETela = Nothing
      End If
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DATela.Seleciona(): " & ex.Message)
    End Try
    Return objBETela
  End Function

  Public Function Seleciona(ByVal objBETela As BETela) As List(Of BETela)
    Dim objBETelas As List(Of BETela) = New List(Of BETela)
    Try
      objBD.duplicaAspasSimples(objBETela, GetType(BETela))
      objQuery._Select = "SELECT * FROM ext_telas_sistema"
      If objBETela IsNot Nothing Then
        If objBETela.IdTela > 0 Then objQuery.Where.AdicionarCondicaoWhere("id_tela", objBETela.IdTela, False)
        objQuery.Where.AdicionarCondicaoWhere("nome", objBETela.Nome)
        objQuery.Where.AdicionarCondicaoWhere("modulo", objBETela.Modulo)
        objQuery.Where.AdicionarCondicaoWhere("descricao", objBETela.Descricao)
      End If
      objBETelas = SelecionaTelas()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DATela.Seleciona(): " & ex.Message)
    End Try
    Return objBETelas
  End Function

  Public Function SelecionaPeloGrupoAcesso(ByVal intIdGrupoAcesso As Integer) As List(Of BETela)
    Dim objBETelas As List(Of BETela) = New List(Of BETela)
    Try
      objQuery._Select = "SELECT ts.* FROM ext_telas_sistema ts " & _
                         "INNER JOIN ext_grupo_acesso_telas_sistema ga_ts " & _
                                 "ON ts.id_tela=ga_ts.id_tela AND " & _
                                    "ga_ts.id_grupo='" & intIdGrupoAcesso & "'"
      objBETelas = SelecionaTelas()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DATela.SelecionaPeloGrupoAcesso(): " & ex.Message)
    End Try
    Return objBETelas
  End Function

  Private Function SelecionaTelas() As List(Of BETela)
    Dim objBETelas As List(Of BETela) = New List(Of BETela)
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBETelas.Add(New BETela(objSQLDReader("id_tela"), _
                                      objSQLDReader("nome").ToString(), _
                                      objSQLDReader("descricao").ToString(), _
                                      objSQLDReader("modulo").ToString()))
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAGrupoAcesso.SelecionaTelas(): " & ex.Message)
    End Try
    Return objBETelas
  End Function

  Public Sub SelecionaPermissoes(ByRef objBETela As BETela, ByVal intIdGrupoAcesso As Integer)
    Dim consultaSql As String = ""
    Try
      consultaSql = "SELECT * FROM ext_grupo_acesso_telas_sistema " & _
                    "WHERE id_tela='" & objBETela.IdTela & "' AND id_grupo='" & intIdGrupoAcesso & "'"
      Using command As New MySqlCommand(consultaSql, BancoDeDados.ConnectionInstance())
        Using reader As MySqlDataReader = command.ExecuteReader()
          While reader.Read()
            objBETela.Leitura = reader("leitura")
            objBETela.Escrita = reader("escrita")
            objBETela.Modificacao = reader("modificacao")
            objBETela.Exclusao = reader("exclusao")
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DATela.SelecionaPermissoes(): " & ex.Message)
    End Try
  End Sub

End Class
